1. Field of the Invention
The invention relates generally to database management system optimization, and more specifically to joining database tables that are partitioned on different attributes or have different number of partitions or partitioned according to a different partitioning scheme.
2. Description of the Background Art
Computer database management systems have become a prevalent means for data storage and retrieval. A database will commonly access the underlying data using a database server system. A client issues a query to the database management system that conforms to a defined query language. This simplifies the client's interaction with the database by not requiring that the client know or understand the underlying structure of the physical database. Because of this, however, it is imperative that the database management system generate and execute the query in a manner which is most efficient for the underlying database.
The database management systems achieve a significant increase in performance by storing data in partitioned database tables. Partitioned database tables allow a query optimizer to generate a query plan that can access data in a particular database table partition rather than the entire database table. However, when the database management system receives a query that joins database tables that are partitioned sub-optimally on attributes that are not a sub-set of columns in the equi-join predicates or have a mismatch in partitioning in terms of the type and number of partitions between the tables, the conventional query optimizer performs a repartition operation on one or both sides of the join operation. The repartition operation typically repartitions the partitions in one or both database tables to match the needed partitioning based on the join predicates and the partitioning of the database tables. The resultant analysis requires a process that re-partitions one or both tables. Such repartition operations are expensive and are likely to decrease the performance of the database management system.
Therefore, what is needed are systems and methods where the database management system is operable to execute a join operation (and/or other database operations) on database tables with an algorithm that allows optimized repartitioning of the data in at least one table prior to the join operation.